import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:get/get.dart';
import '../models/download_record.dart';
import 'user_service.dart';

/// 下载记录数据库服务
class DownloadDatabaseService {
  static Database? _database;
  static const String _tableName = 'download_records';

  /// 获取数据库实例
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  /// 初始化数据库
  Future<Database> _initDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, 'download_records.db');

    return await openDatabase(
      path,
      version: 2, // 升级版本号
      onCreate: _onCreate,
      onUpgrade: _onUpgrade, // 添加升级回调
    );
  }

  /// 创建数据库表
  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE $_tableName (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        device_id TEXT NOT NULL,
        device_name TEXT NOT NULL,
        channel_id TEXT NOT NULL,
        channel_name TEXT NOT NULL,
        video_url TEXT NOT NULL,
        local_path TEXT NOT NULL,
        file_name TEXT NOT NULL,
        start_time TEXT NOT NULL,
        end_time TEXT NOT NULL,
        duration TEXT NOT NULL,
        file_size TEXT NOT NULL,
        downloaded_size TEXT NOT NULL,
        progress REAL NOT NULL,
        speed TEXT NOT NULL,
        status TEXT NOT NULL,
        created_at TEXT NOT NULL,
        updated_at TEXT,
        error_message TEXT,
        pic_url TEXT NOT NULL DEFAULT ''
      )
    ''');
  }

  /// 数据库升级处理
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    print('🔄 数据库升级: $oldVersion -> $newVersion');

    if (oldVersion < 2) {
      // 版本1升级到版本2：添加user_id和pic_url字段
      try {
        await db.execute(
          'ALTER TABLE $_tableName ADD COLUMN user_id INTEGER NOT NULL DEFAULT 0',
        );
        await db.execute(
          'ALTER TABLE $_tableName ADD COLUMN pic_url TEXT NOT NULL DEFAULT ""',
        );
        print('✅ 成功添加 user_id 和 pic_url 字段');
      } catch (e) {
        print('⚠️ 添加字段失败: $e');
        // 如果添加字段失败，尝试重新创建表
        await _recreateTableWithUserBinding(db);
      }
    }
  }

  /// 重新创建表（如果ALTER TABLE失败）
  Future<void> _recreateTableWithUserBinding(Database db) async {
    try {
      print('🔄 尝试重新创建表...');

      // 备份现有数据
      final List<Map<String, dynamic>> existingData = await db.query(
        _tableName,
      );
      print('📋 备份了 ${existingData.length} 条记录');

      // 删除旧表
      await db.execute('DROP TABLE IF EXISTS $_tableName');

      // 创建新表（包含user_id和pic_url字段）
      await db.execute('''
        CREATE TABLE $_tableName (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER NOT NULL,
          device_id TEXT NOT NULL,
          device_name TEXT NOT NULL,
          channel_id TEXT NOT NULL,
          channel_name TEXT NOT NULL,
          video_url TEXT NOT NULL,
          local_path TEXT NOT NULL,
          file_name TEXT NOT NULL,
          start_time TEXT NOT NULL,
          end_time TEXT NOT NULL,
          duration TEXT NOT NULL,
          file_size TEXT NOT NULL,
          downloaded_size TEXT NOT NULL,
          progress REAL NOT NULL,
          speed TEXT NOT NULL,
          status TEXT NOT NULL,
          created_at TEXT NOT NULL,
          updated_at TEXT,
          error_message TEXT,
          pic_url TEXT NOT NULL DEFAULT ''
        )
      ''');

      // 恢复数据（为user_id和pic_url字段设置默认值）
      for (final record in existingData) {
        record['user_id'] = record['user_id'] ?? 0; // 确保user_id有值
        record['pic_url'] = record['pic_url'] ?? ''; // 确保pic_url有值
        await db.insert(_tableName, record);
      }

      print('✅ 表重新创建成功，数据已恢复');
    } catch (e) {
      print('❌ 重新创建表失败: $e');
      // 如果重新创建失败，至少确保表存在
      await db.execute('''
        CREATE TABLE IF NOT EXISTS $_tableName (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER NOT NULL,
          device_id TEXT NOT NULL,
          device_name TEXT NOT NULL,
          channel_id TEXT NOT NULL,
          channel_name TEXT NOT NULL,
          video_url TEXT NOT NULL,
          local_path TEXT NOT NULL,
          file_name TEXT NOT NULL,
          start_time TEXT NOT NULL,
          end_time TEXT NOT NULL,
          duration TEXT NOT NULL,
          file_size TEXT NOT NULL,
          downloaded_size TEXT NOT NULL,
          progress REAL NOT NULL,
          speed TEXT NOT NULL,
          status TEXT NOT NULL,
          created_at TEXT NOT NULL,
          updated_at TEXT,
          error_message TEXT,
          pic_url TEXT NOT NULL DEFAULT ''
        )
      ''');
    }
  }

  /// 插入下载记录
  Future<int> insertDownloadRecord(DownloadRecord record) async {
    final db = await database;
    return await db.insert(_tableName, record.toMap());
  }

  /// 更新下载记录
  Future<int> updateDownloadRecord(DownloadRecord record) async {
    final db = await database;
    final map = record.toMap();
    map['updated_at'] = DateTime.now().toIso8601String();

    // 确保id不为null
    if (record.id == null) {
      print('❌ 更新下载记录失败: record.id 为 null');
      return 0;
    }

    print('📝 数据库更新 - ID: ${record.id}, localPath: ${map['local_path']}');

    final result = await db.update(
      _tableName,
      map,
      where: 'id = ?',
      whereArgs: [record.id],
    );

    print('📝 数据库更新结果: $result 行受影响');
    return result;
  }

  /// 删除下载记录（按用户ID过滤）
  Future<int> deleteDownloadRecord(int id) async {
    final db = await database;
    final userId = await _getCurrentUserId();
    return await db.delete(
      _tableName,
      where: 'id = ? AND user_id = ?',
      whereArgs: [id, userId],
    );
  }

  /// 获取所有下载记录（按用户ID过滤）
  Future<List<DownloadRecord>> getAllDownloadRecords() async {
    final db = await database;
    final userId = await _getCurrentUserId();
    final List<Map<String, dynamic>> maps = await db.query(
      _tableName,
      where: 'user_id = ?',
      whereArgs: [userId],
      orderBy: 'created_at DESC',
    );
    return List.generate(maps.length, (i) => DownloadRecord.fromMap(maps[i]));
  }

  /// 根据状态获取下载记录（按用户ID过滤）
  Future<List<DownloadRecord>> getDownloadRecordsByStatus(
    DownloadStatus status,
  ) async {
    final db = await database;
    final userId = await _getCurrentUserId();
    final List<Map<String, dynamic>> maps = await db.query(
      _tableName,
      where: 'user_id = ? AND status = ?',
      whereArgs: [userId, status.toString().split('.').last],
      orderBy: 'created_at DESC',
    );
    return List.generate(maps.length, (i) => DownloadRecord.fromMap(maps[i]));
  }

  /// 根据ID获取下载记录（按用户ID过滤）
  Future<DownloadRecord?> getDownloadRecordById(int id) async {
    final db = await database;
    final userId = await _getCurrentUserId();
    final List<Map<String, dynamic>> maps = await db.query(
      _tableName,
      where: 'id = ? AND user_id = ?',
      whereArgs: [id, userId],
    );
    if (maps.isNotEmpty) {
      return DownloadRecord.fromMap(maps.first);
    }
    return null;
  }

  /// 检查是否已存在相同的下载记录（按用户ID过滤）
  Future<bool> isDownloadRecordExists(String videoUrl) async {
    final db = await database;
    final userId = await _getCurrentUserId();
    final List<Map<String, dynamic>> maps = await db.query(
      _tableName,
      where: 'video_url = ? AND user_id = ?',
      whereArgs: [videoUrl, userId],
    );
    return maps.isNotEmpty;
  }

  /// 获取下载统计信息（按用户ID过滤）
  Future<Map<String, int>> getDownloadStatistics() async {
    final db = await database;
    final userId = await _getCurrentUserId();
    final List<Map<String, dynamic>> maps = await db.rawQuery(
      '''
      SELECT status, COUNT(*) as count 
      FROM $_tableName 
      WHERE user_id = ?
      GROUP BY status
    ''',
      [userId],
    );

    final Map<String, int> statistics = {};
    for (final map in maps) {
      final status = map['status'] as String;
      final count = map['count'] as int;
      statistics[status] = count;
    }

    return statistics;
  }

  /// 清理已完成的下载记录（按用户ID过滤）
  Future<int> cleanCompletedDownloads() async {
    final db = await database;
    final userId = await _getCurrentUserId();
    return await db.delete(
      _tableName,
      where: 'status = ? AND user_id = ?',
      whereArgs: [DownloadStatus.completed.toString().split('.').last, userId],
    );
  }

  /// 重置数据库（删除所有数据，重新创建表）
  Future<void> resetDatabase() async {
    try {
      print('🔄 重置数据库...');
      final db = await database;

      // 删除现有表
      await db.execute('DROP TABLE IF EXISTS $_tableName');
      print('✅ 旧表删除成功');

      // 重新创建表
      await _onCreate(db, 2);
      print('✅ 新表创建成功');

      // 重置数据库版本
      await db.execute('PRAGMA user_version = 2');
      print('✅ 数据库版本重置成功');
    } catch (e) {
      print('❌ 重置数据库失败: $e');
      rethrow;
    }
  }

  /// 检查数据库结构
  Future<Map<String, dynamic>> checkDatabaseStructure() async {
    try {
      final db = await database;
      final result = await db.rawQuery('PRAGMA table_info($_tableName)');

      final columns = <String, Map<String, dynamic>>{};
      for (final row in result) {
        columns[row['name'] as String] = {
          'type': row['type'],
          'notnull': row['notnull'],
          'default': row['dflt_value'],
        };
      }

      print('📋 数据库表结构检查结果:');
      columns.forEach((name, info) {
        print(
          '  $name: ${info['type']} ${info['notnull'] == 1 ? 'NOT NULL' : 'NULL'} ${info['default'] != null ? 'DEFAULT ${info['default']}' : ''}',
        );
      });

      return columns;
    } catch (e) {
      print('❌ 检查数据库结构失败: $e');
      return {};
    }
  }

  /// 获取下载目录
  Future<String> getDownloadDirectory() async {
    final directory = await getApplicationDocumentsDirectory();
    final downloadDir = Directory('${directory.path}/downloads');
    if (!await downloadDir.exists()) {
      await downloadDir.create(recursive: true);
    }
    return downloadDir.path;
  }

  /// 生成唯一的文件名
  String generateFileName(String deviceName, String startTime, String endTime) {
    final timestamp = DateTime.now().millisecondsSinceEpoch;
    final sanitizedDeviceName = deviceName.replaceAll(RegExp(r'[^\w\s-]'), '');
    return '${sanitizedDeviceName}_${startTime}_${endTime}_$timestamp.mp4';
  }

  /// 获取当前用户ID
  Future<int> _getCurrentUserId() async {
    try {
      final userService = Get.find<UserService>();
      final userId = userService.userId;
      if (userId != null) {
        return userId;
      } else {
        print('⚠️ 用户未登录，使用默认用户ID: 0');
        return 0;
      }
    } catch (e) {
      print('❌ 获取用户ID失败: $e，使用默认用户ID: 0');
      return 0;
    }
  }
}
